using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Sql;
using System.Data;
using System.Xml;
using System.Configuration;
using System.Data.Common;
using System.Data.OleDb;

namespace DataProvider
{
    public class OLEClient:ISqlClient
    {
        private const int timeOut = 0;
        
        #region Connetion String
        private String connStr;
        
        public String ConnectionString
        {
            get
            {
                return connStr;
            }
            set
            {
                connStr = value;
            }
        }
        
        public OLEClient(String dbFileName)
        {
            this.connStr = String.Format("Provider=Microsoft.Jet.OleDb.4.0;Data Source={0}", dbFileName);
        }

        public OLEClient()
        {
            
        }

        public bool Test()
        {
            try
            {
                using (OleDbConnection conn = new OleDbConnection(connStr))
                {
                    conn.Open();
                }
                return true;
            }
            catch
            {
                return false;
            }
        }
        #endregion

        public DataTable Execute(SqlStatemate sqlStatement)
        {
            try
            {
                DataTable result = new DataTable();
                using (OleDbConnection conn = new OleDbConnection(connStr))
                {
                    
                    conn.Open();
                    using (OleDbCommand cmd = new OleDbCommand(sqlStatement.Sql, conn))
                    {
                        cmd.CommandTimeout = timeOut;
                        cmd.CommandType = CommandType.Text;
                        if (sqlStatement.Parameters != null && sqlStatement.Parameters.Count > 0)
                        {
                            cmd.Parameters.AddRange(sqlStatement.Parameters.ToArray());
                        }
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd))
                        {
                            adapter.Fill(result);
                        }
                    }
                }
                return result;
            }
            catch (Exception e)
            {
                DataLayerLog.LogError(e.Message +" " + sqlStatement.Sql);
                throw e;
            }
        }
       
        public DataSet ExecuteDataSet(SqlStatemate sqlStatement)
        {
            try
            {
                DataSet result = new DataSet();
                using (OleDbConnection conn = new OleDbConnection(connStr))
                {
                    conn.Open();
                    using (OleDbCommand cmd = new OleDbCommand(sqlStatement.Sql, conn))
                    {
                        cmd.CommandTimeout = timeOut;
                        cmd.CommandType = CommandType.Text;
                        if (sqlStatement.Parameters != null && sqlStatement.Parameters.Count > 0)
                        {
                            cmd.Parameters.AddRange(sqlStatement.Parameters.ToArray());
                        }
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd))
                        {
                            adapter.Fill(result);
                        }
                    }
                }
                return result;
            }
            catch (Exception e)
            {
                DataLayerLog.LogError(e.Message + " " + sqlStatement.Sql);
                throw e;
            }
        }
        
        public DataTable Execute(String sql)
        {
            try
            {
                SqlStatemate sqlState = new SqlStatemate(sql);
                return this.Execute(sqlState);
            }
            catch (Exception e)
            {
                DataLayerLog.LogError(e.Message + " " + sql);
                throw e;
            }
        }

        public DataTable PagelyExecute(SqlStatemate sqlStatement, int page, int itemCountPerPage)
        {
            try
            {
                page = page < 1 ? 1 : page;
                int startItem = itemCountPerPage * (page - 1);

                DataTable result = new DataTable();
                using (OleDbConnection conn = new OleDbConnection(connStr))
                {
                    conn.Open();
                    using (OleDbCommand cmd = new OleDbCommand(sqlStatement.Sql, conn))
                    {
                        cmd.CommandTimeout = timeOut;
                        cmd.CommandType = CommandType.Text;
                        if (sqlStatement.Parameters != null && sqlStatement.Parameters.Count > 0)
                        {
                            cmd.Parameters.AddRange(sqlStatement.Parameters.ToArray());
                        }
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd))
                        {
                            adapter.Fill(startItem, itemCountPerPage, result);
                        }
                    }
                }
                return result;
            }
            catch (Exception e)
            {
                DataLayerLog.LogError(e.Message + " " + sqlStatement.Sql);
                throw e;
            }
        }

        public DataSet PagelyExecuteReturnDataSet(SqlStatemate sqlStatement, int page, int itemCountPerPage)
        {
            try
            {
                page = page < 1 ? 1 : page;
                int startItem = itemCountPerPage * (page - 1);

                DataSet result = new DataSet();
                using (OleDbConnection conn = new OleDbConnection(connStr))
                {
                    conn.Open();
                    using (OleDbCommand cmd = new OleDbCommand(sqlStatement.Sql, conn))
                    {
                        cmd.CommandTimeout = timeOut;
                        //cmd.Transaction.IsolationLevel = IsolationLevel.ReadUncommitted;
                        cmd.CommandType = CommandType.Text;
                        if (sqlStatement.Parameters != null && sqlStatement.Parameters.Count > 0)
                        {
                            cmd.Parameters.AddRange(sqlStatement.Parameters.ToArray());
                        }
                        using (OleDbDataAdapter adapter = new OleDbDataAdapter(cmd))
                        {
                            adapter.Fill(result,startItem, itemCountPerPage, "Product");
                        }
                    }
                }
                return result;
            }
            catch (Exception e)
            {
                DataLayerLog.LogError(e.Message + " " + sqlStatement.Sql);
                throw e;
            }
        }
       
        public void ExecuteNoResult(String sql)
        {
            try
            {
                SqlStatemate stateMent = new SqlStatemate(sql);
                ExecuteNoResult(stateMent);
            }
            catch (Exception e)
            {
                DataLayerLog.LogError(e.Message + " " + sql);
                throw e;
            }
        }
        
        public void ExecuteNoResult(SqlStatemate sqlStatement)
        {
            try
            {
                if (sqlStatement == null)
                    return;
                using (OleDbConnection conn = new OleDbConnection(connStr))
                {
                    conn.Open();
                    using (OleDbCommand cmd = new OleDbCommand(sqlStatement.Sql, conn))
                    {
                        cmd.CommandTimeout = timeOut;
                        cmd.CommandType = CommandType.Text;
                        if (sqlStatement.Parameters != null && sqlStatement.Parameters.Count > 0)
                        {
                            cmd.Parameters.AddRange(sqlStatement.Parameters.ToArray());
                        }
                        cmd.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception e)
            {
                DataLayerLog.LogError(e.Message + " " + sqlStatement.Sql);
                throw e;
            }
        }

        public void BatchExecute(IList<SqlStatemate> sqlStatementSet)
        {
            try
            {
                if (sqlStatementSet == null)
                    return;
                using (OleDbConnection conn = new OleDbConnection(connStr))
                {
                    conn.Open();
                    OleDbTransaction trans = conn.BeginTransaction();
                    try
                    {
                        foreach (SqlStatemate sqlState in sqlStatementSet)
                        {
                          
                            using (OleDbCommand cmd = new OleDbCommand(sqlState.Sql, conn))
                            {
                                cmd.Transaction = trans;
                                cmd.CommandTimeout = timeOut;
                                cmd.CommandType = CommandType.Text;

                                if (sqlState.Parameters != null && sqlState.Parameters.Count > 0)
                                {
                                    cmd.Parameters.AddRange(sqlState.Parameters.ToArray());
                                }
                                cmd.ExecuteNonQuery();
                            }
                        }
                        trans.Commit();
                    }
                    catch (Exception e)
                    {
                        trans.Rollback();
                        throw e;
                    }
                }
            }
            catch (Exception e)
            {
                DataLayerLog.LogError(e.Message  );
                throw e;
            }
        }

        public String GetSingleResult(SqlStatemate sqlStatement)
        {
            try
            {
                if (sqlStatement == null)
                    return null;

                StringBuilder results = new StringBuilder();
                using (OleDbConnection conn = new OleDbConnection(connStr))
                {
                    conn.Open();
                    using (OleDbCommand cmd = new OleDbCommand(sqlStatement.Sql, conn))
                    {
                        cmd.CommandTimeout = timeOut;
                        if (sqlStatement.Parameters != null && sqlStatement.Parameters.Count > 0)
                        {
                            cmd.Parameters.AddRange(sqlStatement.Parameters.ToArray());
                        }
                        using (OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            do
                            {
                                while (reader.Read())
                                {
                                    Object result = reader.GetValue(0);
                                    results.Append(result.ToString());
                                }
                            } while (reader.NextResult());
                        }
                    }
                }
                return results.ToString();
            }
            catch (Exception e)
            {
                DataLayerLog.LogError(e.Message + " " + sqlStatement.Sql);
                throw e;
            }
        }

        public String GetSingleResult(String sql)
        {
            try
            {
                SqlStatemate state = new SqlStatemate(sql);
                return GetSingleResult(state);
            }
            catch (Exception e)
            {
                DataLayerLog.LogError(e.Message + " " + sql);
                throw e;
            }
        }

        public DbDataReader ExecuteReader(SqlStatemate sqlStatement)
        {
            DbCommand returnCmd = null;
            return ExecuteReader(sqlStatement, ref returnCmd);
        }

        public DbDataReader ExecuteReader(SqlStatemate sqlStatement, ref  DbCommand returnCmd)
        {
            if (sqlStatement == null)
                return null;
            OleDbDataReader reader = null;
            OleDbConnection conn = null;
            try
            {
                conn = new OleDbConnection(connStr);
                conn.Open();
                OleDbCommand cmd = new OleDbCommand(sqlStatement.Sql, conn);
                cmd.CommandTimeout = timeOut;
                cmd.CommandType = CommandType.Text;
                if (sqlStatement.Parameters != null && sqlStatement.Parameters.Count > 0)
                {
                    cmd.Parameters.AddRange(sqlStatement.Parameters.ToArray());
                }
                reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                returnCmd = cmd;
                return reader;
            }
            catch (Exception e)
            {
                DataLayerLog.LogError("Sql Error:" + e.Message + " Sql:" + sqlStatement.Sql);
                if (conn != null && conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    conn.Dispose();
                }
                return null;
            }
        }

        public DbDataReader ExecuteReader(String sql)
        {
            DbCommand returnCmd = null;
            return ExecuteReader(sql,ref returnCmd);
        }

        public DbDataReader ExecuteReader(String sql, ref  DbCommand returnCmd)
        {
            try
            {
                SqlStatemate state = new SqlStatemate(sql);
                return ExecuteReader(state, ref returnCmd);
            }
            catch (Exception e)
            {
                DataLayerLog.LogError(e.Message + " " + sql);
                throw e;
            }
        }

        public bool ExecuteTransaction(IList<SqlStatemate> sqlStatementSet)
        {
           if (sqlStatementSet == null)
                return false;
           using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                conn.Open();
                OleDbTransaction Transaction = conn.BeginTransaction(IsolationLevel.ReadUncommitted);
                try
                {
                    foreach (SqlStatemate sqlState in sqlStatementSet)
                    {
                        using (OleDbCommand cmd = new OleDbCommand(sqlState.Sql, conn))
                        {
                            cmd.CommandTimeout = timeOut;
                            if (sqlState.Parameters != null && sqlState.Parameters.Count > 0)
                            {
                                cmd.Parameters.AddRange(sqlState.Parameters.ToArray());
                            }
                            cmd.ExecuteNonQuery();
                        }
                    }
                    Transaction.Commit();
                    return true;
                }
                catch (Exception e)
                {
                    DataLayerLog.LogError(e.Message);
                    Transaction.Rollback();
                    return false;
                }
            }
        }
       
        public List<string> GetTableList()
        {
            List<String> tableList = new List<string>();
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                conn.Open();
                DataTable table = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "table" });
                foreach (DataRow row in table.Rows) 
                {  
                    tableList.Add(row["table_name"].ToString());
                }
            }
            return tableList;
        }

        public List<TableFieldInfo> GetFieldInfo(string tableName, params string[] reservedFields)
        {
            List<String> reservedFieldSet = new List<string>();
            if (reservedFields != null)
            {
                reservedFieldSet.AddRange(reservedFields);
            }

            List<TableFieldInfo> tableFieldSet = new List<TableFieldInfo>();
            String sql =String.Format("Select * from {0} Where 1=0",tableName);
            using (DbDataReader reader = ExecuteReader(sql))
            {
                List<String> fieldSet = SqlUtils.GetSelectedFields(reader);
                foreach (String fieldName in fieldSet)
                {
                    if (reservedFieldSet.Contains(fieldName))
                    {
                        continue;
                    }
                    TableFieldInfo fieldInfo = new TableFieldInfo();
                    fieldInfo.FieldName = fieldName;
                    tableFieldSet.Add(fieldInfo);
                }
            }
            return tableFieldSet;
        }

        /////////////////////////////////////Do not support/////////////////////////////////////////////////
        public DataTable PagelyExecuteStoreProcedure(string storedProc, int page, int itemCountPerPage, params DbParameter[] parameters)
        {
            throw new NotImplementedException();
        }

        public DataTable ExecuteStoreProcedureReturnTable(string storedProc, params DbParameter[] parameters)
        {
            throw new NotImplementedException();
        }

        public DataSet ExecuteStoreProcedureReturnDataSet(string storedProc, params DbParameter[] parameters)
        {
            throw new NotImplementedException();
        }

        public void ExecuteStoreProcedureNoQuery(string storedProc, params DbParameter[] parameters)
        {
            throw new NotImplementedException();
        }

        public string ExecuteStoreProcedureSingleString(string storedProc, params DbParameter[] parameters)
        {
            throw new NotImplementedException();
        }

        public DbDataReader ExecuteStoreProcedureReader(string storedProc, params DbParameter[] parameters)
        {
            throw new NotImplementedException();
        }

        public DbDataReader ExecuteStoreProcedureReader(string storedProc, ref DbCommand returnCmd, params DbParameter[] parameters)
        {
            throw new NotImplementedException();
        }

    }

}
